CS 1371 - LAB 3 - What is Microsoft Excel?

What is Microsoft Excel?

Microsoft Excel (or just plain old Excel) is a spreadsheet application. Spreadsheets are brilliantly conceived ways to organize data by grouping it into rows and columns. As a promising Tech student, you may never need to look at Excel after this lab. However, if you ever decide to drop out of college and get a job plugging numbers into computers and/or doing mindless administrative chores, Excel may soon become the best seven million lines of code ever written.

Vocabulary

Cell -- the small boxes where data exists in a spreadsheet

Function -- with your extensive knowledge, no definition is necessary

PivotTable -- An interactive table that allows you to reorganize and summarize large amounts of data in a spreadsheet to obtain a desired report.

PivotChart -- An interactive chart that allows you to graphically present a large data set summarized in a Pivot Table.

The Assignment

Section 1

Retrieve the assignment from here and open it using Excel.
(Note: in some browsers you will have to right-click on the link and select 'Save Target As'.)

You will see the following Spreadsheet:

Note that the first column in the spreadsheet is labeled A, and the first row is labeled 1. Similarly, each cell in the Excel spreadsheet has a unique identifier, much like your gt number. Spreadsheets use a coordinate system to access information, so the "Gangstas" cell is at A1 (the "A" column and "1" row). You can access the "Jim Greenlee" field by using A11. A11 and A1 are called "cell references".

Writing Formulas

Let us now work through some sample calculations that will help you complete the rest of the lab. Let's figure out what Jim Greenlee's Exploit Index is.

The Exploit Index is defined as:

Exploit Index = Mad-Genius * Sadism

How can the Excel spreadsheet accomplish the same task? Each cell can either contain data or a formula. Move your cursor to where Jim Greenlee's Exploit Index should go (I11) and click there.

Now, rather than hard-coding the value, let us type in a formula to calculate Greenlee's exploit index. Type:

=D11*E11 (Hit enter)

Note that Excel displays what you type in the formula bar--the row directly above the column guides.

The previous exercise was so interesting that you should now go grab a Coke and turn on some tunes, LOUDLY. You will need it to stay awake through the rest of this lab.

Let's analyze the line that you just typed in. This line multiplies what is in cell D11, Jim's Mad-Genius, with the value from cell E11, Jim's Sadism, and displays the result in the spreadsheet cell I11. By clicking on I11, you can see the exploit index formula displayed in the formula bar. The value you should see in I11 should be a rather mediocre 1764.

Copying Formulas

Now we should calculate the Exploit Index for everyone on our list. We can do this in several ways:

The stoopid way

Repeat the procedure that you just applied on Jim Greenlee to everyone on your list. This is what is called the UGA solution. It is slow and horribly inefficient, but it will give you the correct answer... eventually. Eventually, in CS, is an unacceptable term, so we will liberally deduct points for anyone who solves these problems in this manner.

The better way

Use the cut and paste commands in the 'Edit' menu. When you copy a formula from one cell, the spreadsheet is smart enough to apply that formula to the other cells in a relative fashion. That means that if your formula is in cell I11 and references cells D11 and E11, then when you copy it to I3, it will reference D3 and E3.

Let us walk through this step by excruciating step

1.      Select the cell whose formula you need to copy (I11 in this case)

2.      Select the copy option from the edit menu. This copies the formula.

3.      Now select all the cells to which you want to apply the formula (I3-I19).

Hint: To select many cells at the same time, you can click and hold your mouse button on the first cell, and drag it and then release it on the last cell.

4.      Choose paste from the edit menu.

5.      The calculated results should appear in all these cells.

Note: You can also copy from a cell by selecting it and then point your mouse to the bottom right corner of that cell. The pointer should become a bold cross. Now you simply have to click and drag over the cells you'd like to copy values to.

In the above calculation, we used a relative addressing scheme so that we could copy the formula easily but still make it apply to each individual row. Sometimes, however, we want a cell reference to stay constant. There are ways to use a value in just one particular cell as the source value. This is called absolute cell addressing.

Let's calculate the final value for the Exploit Index. This is accomplished by normalizing the previously calculated value for the Exploit Index (refer to the steps above)

Exploit Index = (Mad-Genius * Sadism) / Normalizer

The value for the normalizer is given in cell B22.

In Excel-speak, the formula for Jim Greenlee's Normalized Exploit Index would be given as:

=(D11*E11)/$B$22 <hit Enter>

The $B$22 means that you will be using the specific value in cell $B$22 for all your formulas, and this WILL NOT change after you use the efficient algorithm to calculate the final values for Exploit Index.

Precision and Formatting

For any number in Excel, you can set the precision for displaying it. Precision controls how many digits are displayed after the decimal point.

How to change a cell's formatting

Select the cell you want to adjust

Right click on the cell

From the pop-up menu that appears, select "Format Cells."

Now format the appropriate values in your spreadsheet for Mad-Genius so that two--and ONLY two-- decimal places are displayed on the screen. To format multiple cells concurrently, select all the cells that you need to format, go to the format menu, select the cell option, and then hunt around for the correct option.

Hint: This should present itself rather obviously under the Number option.

There are other formatting options that are more aesthetically pleasing. For example, you can change the text attributes of the labels, shade them, give them borders and generally make them pretty. Awww. You may beautify your spreadsheet as much as you like. Feel free to be creative, but don't even think about altering any of the values already provided in the spreadsheet.

Graphs

In Excel's context, a graph is defined as a diagram that displays a summary view of the data in a concise form. Below is a table that indicates situations where one type of graph can be more useful than another:

Type of Graph

Useful When

Bar Graph

Comparing one aspect of different categories.

Pie Graph

Comparing percentages

Line Graph

Looking at trends (not necessarily at equally spaced time intervals)

XY Graphs

Looking at trends over equal intervals

Let us apply this to the spreadsheet we have been altering. Show a graph that displays each person's Exploit Index. Put the people on the X-axis and the Exploit Index on the Y-axis.

To plot the graph, go into Insert->Chart.

You are responsible for figuring out how to draw the graph and which type of graph you choose to represent the data. Make sure that you put the graph on the sheet that you have been altering.

For the second part of the lab, please continue to section 2

Section 2

Working with PivotTables

Retrieve the assignment from here and open it using Excel
(Note: in some browsers you will have to right-click on the link and select 'Save Target As'.)

You will see the following Spreadsheet (click picture for enlarged version):

The data are organized into 6 columns containing information on Mean SAT Verbal and Math Scores by State for the last 15 years. Simply put, there is a lot of data here (765 rows by 6 columns). What if there was a way to organize all of this data into an easily viewable format so that we could actually make some use of it? We can do just that by putting all of these data into a PivotTable.

Since SAT scores are most often reported as a total score, the first thing we should do is calculate the Mean Total Score for each state and each year. We do this by writing a formula in each cell in Column G that reports the Mean Total Score, defined as:

Mean Total Score = Mean SAT Verbal + Mean SAT Math

Now, to the fun part, creating our first PivotTable.

Highlight all of the data you want to summarize, including any column titles.

From the Menu Bar, click "Data >> PivotTable and PivotChart Report..."

A dialogue box comes up. From this box, select "Microsoft Office Excel list or database" as the data you want to analyze. Also select "PivotTable" as the kind of report you want to create. Click Next.

On the next screen, verify that the range in the box contains all of the data you want to use. Click Next.

Excel will ask you where you want to put the PivotTable report. Select "New worksheet" and click Finish. There are other options we can select from this screen, but for now we'll leave them as their default values.

A new worksheet will have been added, and your file will now look like this (click picture for enlarged version):

You will notice that there are four areas to which you can add fields to the PivotTable. We could conceivably analyze the data in any number of ways, but for this assignment we'll be looking at the Average of Mean Total Score. To add a field to an area in the PivotTable, simply select it from the PivotTable Field List and drag it to the desired area.

Select the "Region" field and add it to the "Page Fields" area.

Select the "Year" field and add it to the "Column Fields" area.

Select the "State" field and add it to the "Row Fields" area.

Select the "Mean Total Score" field and add it to the "Data Items" area.

You will notice that cell A3 is colored differently than most of the cells, and it says "Sum of Mean Total Score". This tells us how we are summarizing the data. Recall from earlier that we said we wanted to display our data as the Average of Mean Total Score.

To change this, double-click on this cell and select "Average" from the Summarize By box.

Click on the Number button on the right-hand side and select 1 decimal place. This should provide enough precision to differentiate between similar values.

You will now see a PivotTable containing a summarized version of the original data, with averages for each year and each state. The data are sorted alphabetically by state. This is useful, but what if we wanted to see what state has the highest Average SAT Score over the 15-year period?

Select all of the rows containing Mean Total Scores for each state (this should be rows 5 - 55).

From the PivotTable toolbar, select "Sort and Top 10".

Sort the data in Descending order using the field "Average of Mean Total Score".

Now our data are sorted, and we can easily see that Iowa has the highest Average Total Score over the 15-year period. Georgia is third from the bottom.

Working with PivotCharts

We've successfully created a PivotTable in Excel, but what if we wanted a graphical representation of our data? We can accomplish this using a PivotChart.

Go back to the worksheet contianing the original data, and highlight all of the data including the column titles.

From the Menu Bar, click "Data >> PivotTable and PivotChart Report..."

In the dialogue box select "Microsoft Office Excel list or database" as the data you want to analyze.

select "PivotChart report (with PivotTable report)" as the kind of report you want to create. Click Next.

Verify that the range in the box contains all of the data you want to use. Click Next.

Excel will remember that you have just created another PivotTable, and will ask if you want to base the chart on that PivotTable to save memory. Normally we would do this, but for this assignment, click "No".

On the next screen, select "New worksheet" and click Finish.

Both a new worksheet and a new chart will have been added to the file. Excel only uses the worksheet to summarize the data it needs to create the chart, so here we will ignore it and just focus on the chart. The new chart will look like this (click picture for enlarged version):

IMPORTANT: If you have changed the default chart type in Excel, you may have problems creating this PivotChart. If this applies to you: create any chart based on any data in Excel, From the Menu Bar, click "Chart >> Chart Type...", select the Column chart type and click the "Set as Default Chart" button at the bottom of the dialogue box.

As before, there are four areas to which you can add fields to the PivotChart. In creating our PivotChart we'll be looking at the Average Participation Rate by Region. Just as we did before, to add a field to an area in the PivotChart we select it from the PivotTable Field List and drag it to the desired area.

Select the "Region" field and add it to the "Series Fields" area.

Select the "Year" field and add it to the "Category Fields" area.

Select the "State" field and add it to the "Page Fields" area.

Select the "Participation Rate" field and add it to the "Data Items" area.

This time the data are summarized by "Count of Participation Rate", and data are displayed for the years 1990-2004. However, we want to view the data by Average Participation rate. Also, we don't have a complete data set, so we will need to change the years for which we display the data.

To change this, double-click on the button that says "Count of Participation Rate" and select "Average" from the Summarize By box. Also click "Number" and select the "Percentage" category to display the numbers as percentages.

To change the years displayed, click on the arrow next to the "Year" button at the bottom of the chart, and select only the years 1999 - 2004.

We're almost finished, but the data are still a little hard to see. To fix this, we can change the type of chart we've created.

From the Menu Bar, click "Chart >> Chart Type..."

Under the column chart type select the "Clustered Column" chart sub-type (this is the one in the upper left corner).

Now we've finished creating our chart, and we can see that the highest SAT Participation Rate occurs in the Northern states. If we wanted to compare just two regions rather than all four (say, the North and the South), we could click on the arrow next to the "Region" button on the right side of the page and select just the North and South checkboxes.

Our final chart should look like this (click picture for enlarged version):

The Assignment

Get the Excel file you will be editing (you should have done this already if you read the lab).

Calculate the Normalized Exploit Index for all the people listed (you should have done this already if you read the lab).

Fill in the Mad-Genius column to 2 decimal precision (you should have done this already if you read the lab).

Plot a graph of the Normalized Exploit Index vs. Person's Name (you should have done this already if you read the lab).

Fill in the Cruelty column. The formula for Cruelty is:
6.                  
7.                  
   Cruelty = Cruelty Factor * Mercilessness
Hint: The absolute value information will be useful here.

Use Excel to fill in the Penitence column. Penitence is defined as:
9.                  
10.              
   Penitence = 1 / Incorrigibility
You will note, however, that there are a couple of values in the incorrigibility column that have a zero numeric value.
As everyone knows, dividing anything by zero leads to nasty answers. In order to avoid this pitfall, you want to leave those cells blank, read up on the IF feature in Excel.
In Excel the function you need is the 'IF' function. You can find the syntax for the IF function in the Excel on-line help.

In order to get a complete picture of how despicable each villain truly is, calculate the Mean Badness of each gangsta listed in the spreadsheet.
12.              
   Mean Badness = (Mercilessness + Cruelty + Mad-Genius + Incorrigibility) / 4
The translation to Excel-speak is left to you.

After you have calculated the Mean Badness for each and every villain, use Excel's built-in sort feature to sort the villains in ASCENDING order based on their Mean Badness.

Hint: Look in the Data menu option

Create a graph of each villain (X-axis) vs. Mean Badness (Y-axis). Make sure it is on the same spreadsheet as the data.

Save this file as "lab3excel.xls".

The second file you create should contain 3 worksheets and 1 chart. The original data sheet (the one we gave to you) should be in a worksheet called "Data". The first PivotTable you created should be in a worksheet called "Sheet1". The PivotChart you created should be in a worksheet called "Chart1". There is also another worksheet, but the name of this sheet doesn't have to be anything specific.

If you don't name the worksheets/charts EXACTLY as we have asked, you will lose points on your lab assignment.

Call the first excel file "lab3excel.xls" and the second file "lab3SAT.xls".

Alright, sit back, relax, admire your "Excel"lent masterpiece for a few moments... have a refreshing beverage, pat yourself on the back, but don't forget there is another part for the lab...

Important Notes:

Look at direction #16 above….THIS IS IMPORTANT! You will lose points if your worksheets are not named “Data”, “Sheet1”, and “Chart1”.

This link will take you to the second part of the lab (word assignment)

Last Modified: November 17, 2008

	Type of Graph	Useful When
	Bar Graph	Comparing one aspect of different categories.
	Pie Graph	Comparing percentages
	Line Graph	Looking at trends (not necessarily at equally spaced time intervals)
	XY Graphs	Looking at trends over equal intervals